#!/user/bin/env python
# -*-coding:utf-8-*-
'''
@author: Patty
@project: automation_framework
@file: BaseData.py
@time: 2019/5/30 10:50
@IDE: PyCharm 
'''

import logging,traceback,random
import pandas as pd
import base.globalvars as glo
from util.db_util import execute_query
from util.collection_util import random_select_from_list, random_select_one_from_list

framdata = {}


def get_category_id(name):
    """获取指定大类名称id

    :param name: 大类名称

    :return: 大类id
    """
    results = execute_query("SELECT id FROM t_das_category WHERE `name`='%s'" % name)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("get_category_id: %s"%e)
        return None


def get_type_id(category_name, type_name):
    """获取指定大类名称下指定名称的小类id

    :param category_name: 大类名称
    :param type_name: 小类名称

    :return: 小类id
    """
    results = execute_query(
        "SELECT id FROM t_das_type WHERE category_id=(SELECT id FROM t_das_category WHERE `name`='%s') AND `name`='%s'" % (
            category_name, type_name))
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("get_type_id: %s"%e)
        return None


def get_type_ids(category_name, type_names):
    """获取指定大类名称下，指定名称小类的id，以逗号分隔

    :param category_name: 大类名称
    :param type_names: 小类名称列表

    :return: 逗号分隔的小类id字符串
    """
    type_names_str = ','.join(["'"+name+"'" for name in type_names])
    # sql = "SELECT id FROM t_das_type WHERE category_id=(SELECT id FROM t_das_category WHERE `name`='%s') AND `name` in (%s)" \
    #       % (category_name, ','.join(['%s'] * len(type_names)))
    sql = r"SELECT id FROM t_das_type WHERE category_id=(SELECT id FROM t_das_category WHERE `name` = '%s') AND `name` in (%s)" \
          % (category_name,type_names_str)
    results = execute_query(sql)
    try:
        return [row[0] for row in results]
    except Exception as e:
        logging.error("get_type_ids: %s"%e)
        return ""


def get_type_ids_str(category_name, type_names):
    """获取指定大类名称下，指定名称小类的id，以逗号分隔

    :param category_name: 大类名称
    :param type_names: 小类名称列表

    :return: 逗号分隔的小类id字符串
    """
    ids = get_type_ids(category_name,type_names)
    try:
        return ','.join([str(id) for id in ids])
    except Exception as e:
        logging.error("get_type_ids_str: %s"%e)
        return ""


def get_random_category_type_combination():
    """随机获取一个有效的大类、小类组合

    :return: 大类，小类的元组
    """

    results = execute_query(
        "SELECT category_id,id FROM `t_das_type` WHERE id not in (SELECT DISTINCT parent_id FROM t_das_type)")
    category_type_list = list(results)
    category_type = random_select_one_from_list(category_type_list)

    glo.set_value("category_id", category_type[0])
    glo.set_value("type_id", category_type[1])
    return category_type[0], category_type[1]

def get_filetag_id(tag_name):
    """根据标签名获取文件标签id

    :param tag_name: 标签名

    :return: 标签id
    """
    results = execute_query("SELECT id FROM `t_das_file_tag` WHERE `name`='%s'" % tag_name)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("get_filetag_id: %s"%e)
        return None

def get_filetag_ids_str(tag_names):
    """根据标签名的列表，获取对应的标签id字符串，以逗号分隔

    :param tag_names: 标签名列表

    :return: 以逗号分隔的标签id字符串
    """
    tag_names_str = ','.join(tag_names)
    sql = "SELECT id FROM `t_das_file_tag` WHERE `name` in (%s)" % ','.join(['%s'] * len(tag_names))
    results = execute_query(sql, tag_names)
    try:
        ids = [str(row[0]) for row in results]
        return ','.join(ids)
    except Exception as e:
        logging.error("get_filetag_ids: %s"%e)
        return ""

def get_random_file_tags_str(n=1):
    """随机获取n个文件标签id，用逗号连接

    :param n: 标签个数

    :return: 逗号分隔的标签id字符串
    """

    results = execute_query("SELECT id FROM `t_das_file_tag`")
    tag_ids = random_select_from_list([item[0] for item in results], n)
    tag_ids_str = ','.join([str(id) for id in tag_ids])
    glo.set_value("tag_ids_str", tag_ids_str)
    return tag_ids_str


def get_entity_id(name):
    """获取指定实体名称id

    :param name: 实体名称

    :return: 实体id
    """
    results = execute_query("SELECT id FROM `t_das_entity_info` WHERE `entity_name`='%s'" % name)
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("get_entity_id: %s"%e)
        return None


def get_entity_info(org_id):
    """
    获取实体信息

    :param org_id: 实体id

    :return: 实体信息，类型为字典的列表
    """
    #sql = "select id,entity_name,entity_code,entity_type,abbr,sector,sub_sector,market from t_das_entity_info where org_id='%s'" % org_id
    sql = "select * from t_das_entity_info where org_id='%s'" % org_id
    try:
        #res = execute_query(sql)
        res = execute_query(sql, result_type="dict")
        data = pd.Series(res[0],index=["id","entity_name","entity_code","entity_type","abbr","sector","sub_sector","market"])
        return data
    except Exception as e:
        logging.error("get_entity_info: %s"%e)
        traceback.print_exc()
        return pd.Series([])

def get_random_item_codes_str(item_group_name):
    """从t_das_lookup_static表中根据字典名称item_group_name获取随机的字典值item_code

    :param item_group_name: 字典名称

    :return: 字典值
    """

    sql = "select item_code from t_das_lookup_static where item_group='%s'"%item_group_name
    try:
        res = execute_query(sql)

        random_list=random_select_from_list([row[0] for row in res])


    except Exception as e:
        logging.error("get_random_item_codes_str failed: %s"%e)
        traceback.print_exc()
        random_list = []

    result = ','.join(random_list)
    glo.set_value("%s_codes" % item_group_name, random_list)
    glo.set_value("%s_codes_str" % item_group_name, result)
    return result

def get_category_name(category_id):
    """ 获取大类名称

    :param category_id: 大类id

    :return: 大类名称
    """
    sql = "SELECT name from t_das_category where id=%s"%category_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error("get_category_name failed: %s"%e)
        return ""

def get_orgids(markets = None,sectors = None):
    """获取符合条件的org_id列表

    :param markets: 市场列表
    :param sectors: 行业列表

    :return: 符合条件的org_id列表
    """
    if markets is not None:
        markets_str = "','".join(markets)
    else:
        markets_str=""

    if sectors is not None:
        sectors_str = "','".join(sectors)
    else:
        sectors_str = ""

    markets_sub = "and market in ('%s')"%markets_str if markets_str!="" else ""
    sectors_sub = "and sector in ('%s')"%sectors_str if sectors_str!="" else ""
    sql = "select org_id from t_das_entity_info where 1=1 %s %s"%(markets_sub,sectors_sub)
    try:
        res = execute_query(sql)
        return [item[0] for item in res]

    except Exception as e:
        logging.error("get_orgids failed: %s"%e)
        return []


def get_entity_tag_of_entity(entity_code):
    """获取实体的entity_tag
    Todo: 根据org_id调用get_entity_info获取

    :param entity_code: 实体的entity_code

    :return: 实体的entity_tag
    """

    sql = """select entity_tag from t_das_entity_info where entity_code='%s'"""%entity_code

    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error("get_entity_tag_of_entity fail: %s"%e)


def get_remark_id(remark_code=None):
    """通过备注code获取备注ID or code不传时随机获取ID

    :param remark_code: 备注code

    :return: 备注ID
    """

    if remark_code is not None:
        sql = "select id from t_das_remark where code = '%s'" % remark_code
        res = execute_query(sql)
        return res[0][0]
    else:
        sql = "select id from t_das_remark"
    try:
        res = execute_query(sql)
        return [res[i][0] for i in range(len(res))]
    except Exception as e:
        logging.error("get_remark_id fail: %s" % e)


def get_remark_count(name_zh=None):
    """查询备注数量

    :param name_zh: 备注名称

    :return: 备注的总数
    """

    if name_zh is not None:
        remark_name = "where name_zh = '%s'" % name_zh
    else:
        remark_name = ''
    sql = "select count(*) from t_das_remark %s" % remark_name
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error("get_remark_count fail: %s" % e)


def get_extract_bad_id(remark=None):
    """通过坏样备注获取坏样ID

    :param remark：坏样备注

    :return: 最新生成的坏样ID
    """

    if remark is not None:
        remark = remark
    sql = "select id from t_das_extract_bad where remark = '%s' order by crt desc " % remark
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        logging.error("get_extract_bad_id fial: %s" % e)
